package com.power.dao.impl;

import com.power.dao.InformationDao;
import com.power.utils.JdbcBuild;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: 朱雄
 * Date: 2020/10/09 11:33
 * Description:
 * Version: V1.0
 */
public class InformationDaoImpl extends JdbcBuild implements InformationDao {
    @Override
    public List<Map<String, Object>> eliminateAll() throws SQLException {
        String sql="SELECT defecttype.defect_name AS name,COUNT(*) AS value FROM sovle,defecttype WHERE sovle.defect_id = defecttype.defect_id GROUP BY defecttype.defect_name";
        List<Map<String, Object>> list=super.findAllForList(sql,null);
        return list;
    }

    @Override
    public List<Map<String, Object>> eliminateByAccount(String account) throws SQLException {
        String sql="SELECT defecttype.defect_name AS name,COUNT(*) AS value FROM sovle,defecttype WHERE sovle.sovle_account=? and sovle.defect_id = defecttype.defect_id GROUP BY defecttype.defect_name";
        Object[] params={account};
        List<Map<String, Object>> list=super.findAllForList(sql,params);
        return list;
    }

    @Override
    public List<Map<String, Object>> inspectionAll() throws SQLException {
        String sql="SELECT defect_grade.defect_grade_name AS name,COUNT(*) AS value FROM inspection,defect_grade WHERE inspection.defect_grade = defect_grade.defect_id GROUP BY defect_grade.defect_grade_name";
        List<Map<String, Object>> list=super.findAllForList(sql,null);
        return list;
    }

    @Override
    public List<Map<String, Object>> inspectionByAccount(String account,String defect) throws SQLException {
        String sql="SELECT defect_grade.defect_grade_name AS name,COUNT(*) AS value FROM inspection,defect_grade WHERE  inspection.defect_grade = defect_grade.defect_id ";
        List list=new ArrayList();
        if(account !=null && !"".equals(account)){
            sql+=" and inspection.inspection_account=? ";
            list.add(account);
        }
        if(defect !=null && !"".equals(defect)){
            sql+=" and defect_grade.defect_id=? ";
            list.add(defect);
        }
        sql+=" GROUP BY defect_grade.defect_grade_name";
        Object[] params=list.toArray();
        List<Map<String, Object>> list1=super.findAllForList(sql,params);
        return list1;
    }
}
